package net.hn.hnms.biz.safety.trinity.dao;

import net.hn.hnms.biz.safety.trinity.entity.HiddenDangerList;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * @author 智呈物联 nzw
 * @date 2022/8/4
 */
@Repository
public interface HiddenDangerListDao extends JpaRepository<HiddenDangerList,String> {

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tdata_time >= ?1"+
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    List<HiddenDangerList> findAllLikeDataTime(Timestamp timestamp,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1 "+
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    int countAll(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1=1"+
            "\tand (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  "+
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end",nativeQuery = true)
    int gaoTouYaoCountAll(String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),check_date as checkDate \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1 \n"+
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end"+
            " group by check_date",nativeQuery = true)
    List<Map<String,Object>> countAllForDay(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),\n" +
            "\tto_char( check_date, 'yyyy-MM' ) checkDate \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(check_date,'yyyy-MM-dd') >= ?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end \n"+
            "GROUP BY \n" +
            "\tto_char( check_date, 'yyyy-MM' ) \n" +
            "ORDER BY\n" +
            "\tcheckDate",nativeQuery = true)
    List<Map<String,Object>> countAllForMonth(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),\n" +
            "\tto_char( check_date, 'yyyy-MM' ) checkDate \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(check_date,'yyyy-MM-dd') >= ?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end \n"+
            "and (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))"+
            "GROUP BY \n" +
            "\tto_char( check_date, 'yyyy-MM' ) \n" +
            "ORDER BY\n" +
            "\tcheckDate",nativeQuery = true)
    List<Map<String,Object>> gaoTouYaoCountAllForMonth(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),check_date as checkDate \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1 AND ( danger_status = '1' AND approve_status = '1' ) \n"+
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            " group by check_date",nativeQuery = true)
    List<Map<String,Object>> countAllForDayByClose(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(check_date,'yyyy-MM-dd') >= ?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end"+
            "\tAND danger_level = ?3",nativeQuery = true)
    int countAllLevel(String time,String mineCode,String dangerLevel);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end"+
            "\tAND danger_level = ?2 " +
            "and (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))",nativeQuery = true)
    int gaoTouYaoCountAllLevel(String mineCode,String dangerLevel);


    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND danger_status = '延期审批'",nativeQuery = true)
    int countAllDeadline(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(reform_deadline,'yyyy-MM-dd') <= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND danger_status = '3' AND approve_status = '2'",nativeQuery = true)
    int gaoTouYaoCountAllDeadline(String time,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),\n" +
            "\tdanger_level \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(check_date,'yyyy-MM-dd') >= ?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end \n" +
            "GROUP BY \n" +
            "\tdanger_level",nativeQuery = true)
    List<Map<String,Object>> getLevelNum(String time,String mineCode);

    @Query(value = "SELECT\n" +
            "\tdanger_id AS dangerId,\n" +
            "\tdanger_description AS description,\n" +
            "\tdanger_level AS dangerLevel \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(check_date,'yyyy-MM-dd') >= ?1\n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND danger_level = ?3 \n",nativeQuery = true)
    List<Map<String,Object>> queryAll(String time,String mineCode,String level);

    @Query(value = "SELECT\n" +
            "\tdanger_id AS dangerId,\n" +
            "\tdanger_description AS description,\n" +
            "\tdanger_level AS dangerLevel \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t1=1\n" +
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end" +
            "\tAND danger_level = ?2 \n"+
            " and (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))",nativeQuery = true)
    List<Map<String,Object>> gaoTouYaoQueryAll(String mineCode,String level);


    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND (danger_status = '待五落实' OR danger_status IS NULL OR danger_status = '待返单')",nativeQuery = true)
    int rectified(String date,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1=1  \n" +
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end" +
            "\tAND danger_status = '0' and  approve_status ='1'",nativeQuery = true)
    int notRectified(String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND danger_status = ?3",nativeQuery = true)
    int ClosedLoopCount(String date,String mineCode,String dangerStatus);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1=1 \n" +
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end" +
            "\tAND danger_status = ?2 and approve_status ='1'",nativeQuery = true)
    int gaoTouYaoClosedLoopCount(String mineCode,String dangerStatus);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND (danger_status = '待复查' or danger_status = '待销号')",nativeQuery = true)
    int pendingInspection(String date,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1=1  \n" +
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end" +
            "\tAND (danger_status = '3')",nativeQuery = true)
    int inRectification(String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND (danger_status in (?3) OR danger_status IS NULL )",nativeQuery = true)
    int NotClosedLoopCount(String date,String mineCode,String dangerStatus);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end" +
            "\tAND (danger_status = '0' and  approve_status = '1')",nativeQuery = true)
    int NotGaoTouYaoClosedLoopCount(String date,String mineCode,String dangerStatus);


    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  ",nativeQuery = true)
    int countAll();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t1 = 1 \n" +
            "\tAND danger_level = '1'",nativeQuery = true)
    int countAllLevel();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),mine_code \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t1 = 1 \n" +
            "\tAND danger_level = '1'"+
            "\tAND(( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' )) "+
            "AND to_char( reform_deadline, 'yyyy-MM-dd' ) > to_char( now(), 'yyyy-MM-dd' )"+
            "GROUP BY mine_code",nativeQuery = true)
    List<Map<String,Object>> countOverdueMajorHiddenDangerByMineCode();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ),mine_code \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t1 = 1 \n" +
            "\tAND danger_level = '1'"+
            "\tAND(( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  "+
            "GROUP BY mine_code",nativeQuery = true)
    List<Map<String,Object>> countAllLevelByMineCode();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1=1  \n" +
            "\tAND danger_status = '0'",nativeQuery = true)
    int rectified();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t to_char(check_date,'yyyy-MM-dd') >= ?1  \n" +
            "\tAND danger_status = '延期审批'",nativeQuery = true)
    int countAllDeadline(String time);

    @Query(value = "SELECT\n" +
            "\tcount(1) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char(data_time,'yyyy-MM-dd') >= ?",nativeQuery = true)
    int findAllLikeDataTime(String date);

    @Query(value = "SELECT\n" +
            "\tcount(1),mine_code \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t  CASE when ?2 != '' then company_code = ?2 else 1=1 end" +
            "\t and to_char(data_time,'yyyy-MM-dd') >= ?1 group by mine_code",nativeQuery = true)
    List<Map<String,Object>> findAllLikeDataTimeByMineCode(String date,String companyCode);

    @Query(value = "SELECT\n" +
            "\tcount(1),to_char(data_time,'yyyy-MM-dd') dataTime \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "\t where CASE when ?1 != '' then company_code = ?1 else 1=1 end" +
            " group by dataTime",nativeQuery = true)
    List<Map<String,Object>> findAllByDataTime(String companyCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1 = 1  \n" +
            "\tAND danger_status = '1'",nativeQuery = true)
    int ClosedLoopCount();

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) ,mine_code\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t CASE when ?1 != '' then trim(company_code) = ?1 else 1=1 end" +
            "\tAND ( danger_status = '1' AND approve_status = '1' ) group by mine_code",nativeQuery = true)
    List<Map<String,Object>> closedLoopCountByMineCode(String companyCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) ,to_char(data_time,'yyyy-MM-dd') dataTime\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1 = 1  \n" +
            "\tand CASE when ?1 != '' then trim(company_code) = ?1 else 1=1 end" +
            "\tAND ( danger_status = '1' AND approve_status = '1' ) group by dataTime",nativeQuery = true)
    List<Map<String,Object>> closedLoopCountByDataTime(String companyCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t 1 = 1  \n" +
            "\tAND (danger_status = '3')",nativeQuery = true)
    int NotClosedLoopCount();

    @Query(value = "SELECT COUNT\n" +
            "\t( 1 ),\n" +
            "\tmine_code \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  " +
            "GROUP BY\n" +
            "\tmine_code",nativeQuery = true)
    List<Map<String,Object>> countAllByMineCode(String date);


    @Query(value = "SELECT\n" +
            "\tdanger_status,\n" +
            "\tCOUNT ( 1 )\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char( check_date, 'yyyy-MM-dd' ) >= ?1 \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) <= ?2 \n" +
            "\t AND (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  " +
            "GROUP BY\n" +
            "\tdanger_status",nativeQuery = true)
    List<Map<String,Object>> hiddenDangerState(String startTime, String endTime);

    @Query(value = "SELECT\n" +
            "\tdanger_level,\n" +
            "\tCOUNT ( 1 )\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char( check_date, 'yyyy-MM-dd' ) >= ?1 \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) <= ?2 \n" +
            "\t AND (( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  " +
            "GROUP BY\n" +
            "\tdanger_level",nativeQuery = true)
    List<Map<String,Object>> hiddenDangerLevel(String startTime, String endTime);

    @Query(value = "SELECT\n" +
            "\tdanger_level,\n" +
            "\tCOUNT ( 1 )\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char( check_date, 'yyyy-MM-dd' ) >= ?1 \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) <= ?2 \n" +
            "\tAND mine_code= ?3 \n" +
            "GROUP BY\n" +
            "\tdanger_level",nativeQuery = true)
    List<Map<String,Object>> hiddenDangerLevel(String startTime, String endTime,String mineCode);

    @Query(value = "SELECT\n" +
            "\tmine_code \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char( check_date, 'yyyy-MM-dd' ) >= ?1 \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) <= ?2 \n" +
            "GROUP BY\n" +
            "\tmine_code",nativeQuery = true)
    List<String> hiddenDangerMineCode(String startTime, String endTime);

    @Query(value = "SELECT\n" +
            "\tcheck_date,\n" +
            "\tCOUNT ( 1 )\n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\tto_char( check_date, 'yyyy-MM-dd' ) >= ?1 \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) <= ?2 \n" +
            "GROUP BY\n" +
            "\tcheck_date",nativeQuery = true)
    List<Map<String,Object>> hiddenDangerDataTime(String startTime, String endTime);


    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "(( danger_status = '1' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\tOR ( danger_status = '3' AND approve_status = '2' ))  " +
            " and danger_level = '1'",nativeQuery = true)
    int majorDangerCount();

    @Query(value = "SELECT\n" +
            "\tcount(*) \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\treform_deadline < NOW( ) \n" +
            "\tAND ( danger_status = '3' AND approve_status = '2' )",nativeQuery = true)
    int majorOverdueCount();


    @Query(value = "SELECT COUNT\n" +
            "\t( 1 ),\n" +
            "\tdanger_level \n" +
            "FROM\n" +
            "\thidden_danger_list \n" +
            "WHERE\n" +
            "\t(\n" +
            "\t\t( danger_status = '1' AND approve_status = '1' ) \n" +
            "\t\tOR ( danger_status = '0' AND approve_status = '1' ) \n" +
            "\t\tOR ( danger_status = '3' AND approve_status = '2' ) \n" +
            "\t) \n" +
            "\tAND to_char( check_date, 'yyyy-MM-dd' ) >= ? \n" +
            "GROUP BY\n" +
            "\tdanger_level",nativeQuery = true)
    List<Map<String,Object>> getHiddenDangerLevelYear(String dataTime);

}
